Using mapped sprocs and reflection to do complex updates in Entity Framework
1. Get a table class instance
ItemMast itemMastRes = (from rec in _context.ItemMast where rec.ItemID == iid select rec).FirstOrDefault();
2. You need an indexing partial class on the table typw
public partial class ItemMast
{
public object this[string propertyName]
{
get { return this.GetType().GetProperty(propertyName).GetValue(this, null); }
set { this.GetType().GetProperty(propertyName).SetValue(this, value, null); }
}
}
3. Map the update sproc to the table in entity framework diagram
4. Start with a list of values to be changes
public class TableItemPost
{
public string itemId { get; set; }
public string table { get; set; }
public string field { get; set; }
public string fieldValue { get; set; }
}
5. Fill the table type
itemMastRes = Utilities.FillItemTable(tableItemPosts, itemMastRes);
This processes itemMastRes in a set of methods and returns a full table.
6. The methods use ‘dynamic’ for simple reflection
public static dynamic FillItemTable(List<TableItemPost> productDetailPosts, dynamic tableIn)
{
foreach (TableItemPost productDetailPost in productDetailPosts)
{
string filedType = GetCastedType(productDetailPost.field, tableIn);
switch (filedType)
{
case "Decimal":
tableIn[productDetailPost.field] = decimal.Parse(productDetailPost.fieldValue);
break;
case "Int":
tableIn[productDetailPost.field] = int.Parse(productDetailPost.fieldValue);
break;
case "DateTime":
tableIn[productDetailPost.field] = DateTime.Parse(productDetailPost.fieldValue);
break;
default:
tableIn[productDetailPost.field] = productDetailPost.fieldValue;
break;
}
}
return tableIn;
}
public static string GetCastedType(string field, object tableIN)
{
string typeOut = "";
foreach (var tableField in tableIN.GetType().GetProperties())
{
if (tableField.Name == field)
{
typeOut = tableField.PropertyType.GetGenericArguments()[0].Name;
}
}
return typeOut;
}
7. Finally apply the values
context.SaveChanges();
Sql group by with count
List<ListCard> withLeads = (from lst in zbe.lists
join pros in zbe.prospects on lst.listId equals pros.listFK
join ld in zbe.leads on pros.prospectId equals ld.prospectFk
where lst.userFk == userId && (lst.listType == proximity || lst.listType == postal)
group lst by lst.listId into lcard
select new ListCard
{
listId = lcard.FirstOrDefault().listId,
dateCreate = lcard.FirstOrDefault().dateCreate,
prospectCount = lcard.FirstOrDefault().prospectCount,
proscessedProspects = lcard.Count(),
listType = lcard.FirstOrDefault().listType,
term = lcard.FirstOrDefault().term,
location = lcard.FirstOrDefault().location
}).ToList();
List<ListCard> fulList = zbe.lists.Where(a => a.userFk == userId && (a.listType == proximity || a.listType == postal))
.Select(a => new ListCard
{
listType = a.listType,
location = a.location,
term = a.term,
proscessedProspects = 0,
prospectCount = a.prospectCount,
dateCreate = a.dateCreate,
listId = a.listId
}).ToList();
// could possibly just put this iif into the fullist
List<ListCard> combinedList = (from fl in fulList
select new ListCard
{
listId = fl.listId,
dateCreate = fl.dateCreate,
prospectCount = fl.prospectCount,
proscessedProspects = ((from recnum in withLeads where recnum.listId == fl.listId select recnum).FirstOrDefault() !=null ? (from recnum in withLeads where recnum.listId == fl.listId select recnum).FirstOrDefault().proscessedProspects : 0),
listType = fl.listType,
term = fl.term,
location = fl.location
}).OrderByDescending(a => a.dateCreate).ToList();
simple concatenate of two lists..
List<ServiceRequest> results = dailyLettersBatchLOR.Union(dailyLettersBatchCRED).ToList();
To compare a date and not just a date and time
DateTime dQuery = new DateTime(todayDate.Year, todayDate.Month, todayDate.Day);
var recs = _rep.GetServiceRequests().Where(a => EntityFunctions.TruncateTime(a.DueDate) == dQuery).ToList();
Inline if in select statement
honame = rec.HandlingOption !=null ? rec.HandlingOption.Name : null
AHHHH Contain clause (simple ... once you get it.)
First the main clause
List<RecipeIng> recings2 = (from recing in recings1
where recs2.Contains(recing,new RecipeCompare())
select new RecipeIng { recId = recing.recId, foomasId = recing.foomasId }).Distinct().ToList();
Then the comparator
public class RecipeCompare : IEqualityComparer<RecipeIng>
{
public bool Equals(RecipeIng x, RecipeIng y)
{
return x.recId == y.recId;
}
public int GetHashCode(RecipeIng codeh)
{
return codeh.recId.GetHashCode();
}
}
And you can use the
comparator in a distinct query ... like this
recs3 = (from rbase in recings1 select rbase).Distinct(new RecipeCompare()).ToList();
Logger that works in Repository using reflection
private void LogChanges(int recordId, int userId)
{
string changedFields = "";
string originalValues = "";
string newValues = "";
string tableName = "";
foreach (var ent in _context.ChangeTracker.Entries().Where(p => p.State == System.Data.EntityState.Added || p.State == System.Data.EntityState.Deleted || p.State == System.Data.EntityState.Modified))
{
tableName = ent.Entity.GetType().Name.Split('_')[0];
foreach (var cv in ent.CurrentValues.PropertyNames)
{
DbPropertyEntry propertyEntry = ent.Property(cv);
if (propertyEntry.IsModified)
{
if (changedFields.Length == 0)
{
changedFields = cv;
}
else
{
changedFields += ";" + cv;
}
if (originalValues.Length == 0)
{
originalValues = propertyEntry.OriginalValue.ToString();
}
else
{
originalValues += ";" + propertyEntry.OriginalValue.ToString();
}
if (newValues.Length == 0)
{
newValues = propertyEntry.CurrentValue.ToString();
}
else
{
newValues += ";" + propertyEntry.CurrentValue.ToString();
}
}
string sline = cv + "=" + ent.CurrentValues[cv];
}
}
InsertActionLog(userId, tableName, recordId, changedFields, originalValues, newValues, loggedInUser);
}
Getting Distinct to work with objects
First you need an object
public class Storeroom
{
public string location { get; set; }
public string Description { get; set; }
}
Then a comparer class
public class StoreroomCompare : IEqualityComparer<Storeroom>
{
public bool Equals(Storeroom left, Storeroom right)
{
if ((object)left == null && (object)right == null)
{
return true;
}
if ((object)left == null || (object)right == null)
{
return false;
}
return left.location == right.location;
}
public int GetHashCode(Storeroom storeroom)
{
return (storeroom.location).GetHashCode();
}
}
Finally this is the call
viewModel.storerooms_locations = datain.Select(x => new Storeroom() { Description = x.Field<string>("DESCRIPTION"), location = x.Field<string>("LOCATION") }).Distinct(new StoreroomCompare()).ToList();
'Left join' into new object with fields from both tables
List<JobCalendarDetail> jds = (from j in db.Jobs
join c in db.Contracts on j.id equals c.job.id into tempJobs
from c in tempJobs.DefaultIfEmpty()
where j.PharmacyId == SessionPersister.PharmacyId
select new JobCalendarDetail { jobId = j.id, jobTitle = j.jobTitle, endDate = j.endDate.ToShortDateString(), startDate = j.startDate.ToShortDateString(), contractId = c.id }).ToList();
Get a list of object from lists that are n levels deep in a object hierarchy using 'into'
var ausers = from ret in db.Pharmacies where ret.Company.id == SessionPersister.CompanyId select ret.pharmacists into pharms from mgrs in pharms
where (mgrs.user.last_Name.StartsWith(term) || mgrs.user.userName.StartsWith(term) || mgrs.user.first_Name.StartsWith(term))
select new Autocomplete { label = mgrs.user.first_Name + " " + mgrs.user.last_Name, value = mgrs.user.first_Name + " " + mgrs.user.last_Name, id = mgrs.user.id };
another technique
List<Pharmacy> pharms = (from b in db.Pharmacies from a in b.pharmacists where a.user.id.Equals(SessionPersister.UserId) select b).ToList();
A Linq Union
var ausers = (from ret in db.Pharmacies
where ret.Company.id == SessionPersister.CompanyId
select ret.pharmacists into pharms
from mgrs in pharms
where (mgrs.user.last_Name.StartsWith(term) || mgrs.user.userName.StartsWith(term) || mgrs.user.first_Name.StartsWith(term))
select new Autocomplete { label = mgrs.user.first_Name + " " + mgrs.user.last_Name, value = mgrs.user.first_Name + " " + mgrs.user.last_Name, id = mgrs.user.id })
.Union(from c in db.Companys
from a in c.admins
where c.id.Equals(SessionPersister.CompanyId)
where (a.user.last_Name.StartsWith(term) || a.user.userName.StartsWith(term) || a.user.first_Name.StartsWith(term))
select new Autocomplete { label = a.user.first_Name + " " + a.user.last_Name, value = a.user.first_Name + " " + a.user.last_Name, id = a.user.id });
Get a list of objects from inside another object.
List<BillingInfo> bi = db.Companys.Where(a => a.id == id).Select(a => a.billingInfos).SingleOrDefault().ToList();
LINQ group by (from answer on Stack overflow by Anders Hejlsberg)
First make a class to contain the answer
public class PaymentList
{
public int paymentTypeId { get; set; }
public string pharmacyName { get; set; }
public decimal amount { get; set; }
public int countTrans { get; set; }
}
Then do the linq call
This statement returns the sum for the t=== ===
'Left join' into new object with fields from both tables
List<JobCalendarDetail> jds = (from j in db.Jobs
join c in db.Contracts on j.id equals c.job.id into tempJobs
from c in tempJobs.DefaultIfEmpty()
where j.PharmacyId == SessionPersister.PharmacyId
select new JobCalendarDetail { jobId = j.id, jobTitle = j.jobTitle, endDate = j.endDate.ToShortDateString(), startDate = j.startDate.ToShortDateString(), contractId = c.id }).ToList();
Get a list of object from lists that are n levels deep in a object hierarchy using 'into'
var ausers = from ret in db.Pharmacies where ret.Company.id == SessionPersister.CompanyId select ret.pharmacists into pharms from mgrs in pharms
where (mgrs.user.last_Name.StartsWith(term) || mgrs.user.userName.StartsWith(term) || mgrs.user.first_Name.StartsWith(term))
select new Autocomplete { label = mgrs.user.first_Name + " " + mgrs.user.last_Name, value = mgrs.user.first_Name + " " + mgrs.user.last_Name, id = mgrs.user.id };
another technique
List<Pharmacy> pharms = (from b in db.Pharmacies from a in b.pharmacists where a.user.id.Equals(SessionPersister.UserId) select b).ToList();
A Linq Union
var ausers = (from ret in db.Pharmacies
where ret.Company.id == SessionPersister.CompanyId
select ret.pharmacists into pharms
from mgrs in pharms
where (mgrs.user.last_Name.StartsWith(term) || mgrs.user.userName.StartsWith(term) || mgrs.user.first_Name.StartsWith(term))
select new Autocomplete { label = mgrs.user.first_Name + " " + mgrs.user.last_Name, value = mgrs.user.first_Name + " " + mgrs.user.last_Name, id = mgrs.user.id })
.Union(from c in db.Companys
from a in c.admins
where c.id.Equals(SessionPersister.CompanyId)
where (a.user.last_Name.StartsWith(term) || a.user.userName.StartsWith(term) || a.user.first_Name.StartsWith(term))
select new Autocomplete { label = a.user.first_Name + " " + a.user.last_Name, value = a.user.first_Name + " " + a.user.last_Name, id = a.user.id });
Get a list of objects from inside another object.
List<BillingInfo> bi = db.Companys.Where(a => a.id == id).Select(a => a.billingInfos).SingleOrDefault().ToList();
LINQ group by (from answer on Stack overflow by Anders Hejlsberg)
First make a class to contain the answer
public class PaymentList
{
ran type and a count of the grouped records
List<PaymentList> retVal = (from pay in db.Payments
group pay by new { pay.contract.pharmacy.pharmacyName, pay.PaymentTypeId }
into g
let CountTrans = g.Count()
select new PaymentList { paymentTypeId = g.Key.PaymentTypeId, pharmacyName = g.Key.pharmacyName, amount = g.Sum(a => a.amount), countTrans = CountTrans }).ToList();
===Execute random SQL with LINQ===
ObjectContext ctx = ((IObjectContextAdapter)db).ObjectContext;
ctx.ExecuteStoreCommand("delete from ContractorDetails");
===get first occurance===
Customer customer = list.FirstOrDefault(cus => cus.FirstName=="John");
===enumerate hidden files===
static void Main(string[] args){ // dir is a collection of IEnumerable<string>
var dir = new DirectoryInfo(@"C:\")
.EnumerateDirectories()
.Where(x => x.Attributes.HasFlag(FileAttributes.Hidden))
.Select(x => x.Name);
foreach (var file in dir)
{
Console.WriteLine(file);
}
Console.ReadLine();
}
===<span class="pun"> Eager Load</span>===
[[code format="csharp"]]
User user = (from ret in db.Users.Include(u => u.userType)
where ret.userName == userIn.userName
&& ret.userPassword == userIn.userPassword select ret)
.FirstOrDefault();
Eager load of complex object
Pharmacy curPharmacy = db.Pharmacies.Where(p => p.id.Equals(pid)).Include(pharmacy => pharmacy.pharmacists.Select(pharm=>pharm.user)).FirstOrDefault();
Find an element by matching an element inside a list inside an element
Pharmacy pharmacy = (from b in db.Pharmacys from a in b.pharmacists where a.id.Equals(PharmacistId) select b).FirstOrDefault();
or
Find an object inside a list of objects
Pharmacy pharmacy = (from ret in db.Pharmacies where ret.pharmacists.Any(ph => ph.id.Equals(userPharmacist.id)) select ret).FirstOrDefault();